#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
Author:Lijiacai
Email:1050518702@qq.com
===========================================
CopyRight@JackLee.com
===========================================
"""
user_password = {
    "iot": "iot",  # 科技中心
}

es_index = "test24"
vehicle_base_info_index = "vehicle_base_info_test24"

order_flow_sql = {
    "sql":
        """
        SELECT
            BIZ_ORDER_ITEM.LICENSE,
            BIZ_ORDER_ITEM.VEHICLE_TYPE_NAME,
            BIZ_ORDER.ORDER_NO,
            BIZ_ORDER.CUSTOMER_NAME,
            BIZ_ORDER.CONTACTOR_PHONE,
            CONCAT( BIZ_ORDER.CUSTOMER_NAME, BIZ_ORDER.CONTACTOR_PHONE ) AS CUSTOMER_HEAD,
            CONCAT( '用', '户' ) AS ROLE,
            BIZ_CUSTOMER.REG_TIME,
            RES_CITY.CITY_NAME,
            BIZ_ORDER_ITEM.REAL_GET_TIME,
            BIZ_ORDER_ITEM.REAL_RET_TIME,
            RSS1.STATION_NAME AS GET_STATION_NAME,
            RSS2.STATION_NAME AS RET_STATION_NAME,
            RSS1.STATION_NAME,
            RSS1.STATION_ADDRESS,
            CASE
                RSS1.IS_GUARDING 
                WHEN 0 THEN
                '没有' 
                WHEN 1 THEN
                '有' ELSE '' 
                END IS_GUARDING,
            CASE
                RSS1.USE_TYPE 
                WHEN 0 THEN
                '普通' 
                WHEN 1 THEN
                '换电站' 
                WHEN 2 THEN
                '充电站' ELSE '' 
                END USE_TYPE,
            BIZ_ORDER.CREATE_TIME,
            BIZ_ORDER.MOD_TIME,
            BIZ_ORDER.PAY_TYPE,
            SYS_CODE_VALUE.CODE_NAME AS ORDER_STATUS,
            BIZ_ORDER.TOTAL_AMOUNT,
            BIZ_ORDER.TOTAL_REDUCE_AMOUNT,
            BIZ_ORDER.PAYED_AMOUNT,
            BIZ_ORDER_FLOW.CREATETIME AS ACTION_TIME,
            BIZ_ORDER_FLOW.RECORDDECL AS CONTENT 
        FROM
            PANDA.BIZ_ORDER BIZ_ORDER,
            PANDA.BIZ_ORDER_FLOW BIZ_ORDER_FLOW,
            PANDA.SYS_CODE_VALUE SYS_CODE_VALUE,
            PANDA.BIZ_CUSTOMER BIZ_CUSTOMER,
            PANDA.BIZ_CUSTOMER_BLGCTY BIZ_CUSTOMER_BLGCTY,
            PANDA.RES_CITY RES_CITY,
            PANDA.BIZ_ORDER_ITEM BIZ_ORDER_ITEM
            LEFT JOIN PANDA.RES_SERVICE_STATION RSS1 ON BIZ_ORDER_ITEM.GET_STATION_ID = RSS1.ID
            LEFT JOIN PANDA.RES_SERVICE_STATION RSS2 ON BIZ_ORDER_ITEM.REAL_RET_STATION_ID = RSS2.ID
        WHERE
            BIZ_ORDER.ORDER_NO = BIZ_ORDER_ITEM.ORDER_NO 
            AND BIZ_ORDER_FLOW.ORDERNO = BIZ_ORDER_ITEM.ORDER_NO 
            AND SYS_CODE_VALUE.TYPE_ID = '1023' 
            AND BIZ_CUSTOMER.ID = BIZ_ORDER.CUSTOMER_ID
            AND BIZ_CUSTOMER_BLGCTY.CST_ID = BIZ_ORDER.CUSTOMER_ID
            AND RES_CITY.CITY_CODE = BIZ_CUSTOMER_BLGCTY.REGISTER_CITY
            AND SYS_CODE_VALUE.CODE_VALUE = BIZ_ORDER.ORDER_STATUS 
            AND TO_CHAR( BIZ_ORDER_ITEM.CREATE_TIME, 'yyyy-mm-dd' ) >= '{}'
            AND TO_CHAR( BIZ_ORDER_ITEM.CREATE_TIME, 'yyyy-mm-dd' ) <= '{}'
        """,
    "fields":
        ["LICENSE",
         "VEHICLE_TYPE_NAME",
         "ORDER_NO",
         "CUSTOMER_NAME",
         "CONTACTOR_PHONE",
         "CUSTOMER_HEAD",
         "ROLE",
         "REG_TIME",
         "CITY_NAME",
         "REAL_GET_TIME",
         "REAL_RET_TIME",
         "GET_STATION_NAME",
         "RET_STATION_NAME",
         "STATION_NAME",
         "STATION_ADDRESS",
         "IS_GUARDING",
         "USE_TYPE",
         "CREATE_TIME",
         "MOD_TIME",
         "PAY_TYPE",
         "ORDER_STATUS",
         "TOTAL_AMOUNT",
         "TOTAL_REDUCE_AMOUNT",
         "PAYED_AMOUNT",
         "ACTION_TIME",
         "CONTENT"],
}

order_cancle_sql = {
    "sql":
        """
        SELECT
            BIZ_ORDER_ITEM.LICENSE,
            BIZ_ORDER_ITEM.VEHICLE_TYPE_NAME,
            BIZ_ORDER.ORDER_NO,
            BIZ_ORDER.CUSTOMER_NAME,
            BIZ_ORDER.CONTACTOR_PHONE,
            CONCAT( BIZ_ORDER.CUSTOMER_NAME, BIZ_ORDER.CONTACTOR_PHONE ) AS CUSTOMER_HEAD,
            CONCAT( '用', '户' ) AS ROLE,
            BIZ_CUSTOMER.REG_TIME,
            RES_CITY.CITY_NAME,
            BIZ_ORDER_ITEM.REAL_GET_TIME,
            BIZ_ORDER_ITEM.REAL_RET_TIME,
            RSS1.STATION_NAME AS GET_STATION_NAME,
            RSS2.STATION_NAME AS RET_STATION_NAME,
            RSS1.STATION_NAME,
            RSS1.STATION_ADDRESS,
            CASE RSS1.IS_GUARDING
                WHEN 0 THEN
                    '没有'
                WHEN 1 THEN
                    '有'
                ELSE
                    ''
            END IS_GUARDING,
            CASE RSS1.USE_TYPE
                WHEN 0 THEN
                    '普通'
                WHEN 1 THEN
                    '换电站'
                WHEN 2 THEN
                    '充电站'
                ELSE
                    ''
            END USE_TYPE,
            BIZ_ORDER.CREATE_TIME,
            BIZ_ORDER.MOD_TIME,
            BIZ_ORDER.PAY_TYPE,
            SYS_CODE_VALUE.CODE_NAME AS ORDER_STATUS,
            BIZ_ORDER.TOTAL_AMOUNT,
            BIZ_ORDER.TOTAL_REDUCE_AMOUNT,
            BIZ_ORDER.PAYED_AMOUNT,
            BIZ_ORDER_CANCEL_RECORD.CREATE_TIME AS ACTION_TIME,
            CONCAT( SYS_CODE_VALUE_1.CODE_NAME, BIZ_ORDER_CANCEL_RECORD.REMARK ) AS CONTENT 
        FROM
            PANDA.BIZ_ORDER BIZ_ORDER,
            PANDA.BIZ_ORDER_CANCEL_RECORD BIZ_ORDER_CANCEL_RECORD,
            PANDA.SYS_CODE_VALUE SYS_CODE_VALUE_1,
            PANDA.SYS_CODE_VALUE SYS_CODE_VALUE,
            PANDA.BIZ_CUSTOMER BIZ_CUSTOMER,
            PANDA.BIZ_CUSTOMER_BLGCTY BIZ_CUSTOMER_BLGCTY,
            PANDA.RES_CITY RES_CITY,
            PANDA.BIZ_ORDER_ITEM BIZ_ORDER_ITEM
            LEFT JOIN PANDA.RES_SERVICE_STATION RSS1 ON BIZ_ORDER_ITEM.GET_STATION_ID = RSS1.ID
            LEFT JOIN PANDA.RES_SERVICE_STATION RSS2 ON BIZ_ORDER_ITEM.REAL_RET_STATION_ID = RSS2.ID 
        WHERE
            BIZ_ORDER.ORDER_NO = BIZ_ORDER_ITEM.ORDER_NO 
            AND SYS_CODE_VALUE_1.TYPE_ID = '1089' 
            AND SYS_CODE_VALUE_1.CODE_VALUE = BIZ_ORDER_CANCEL_RECORD.REASON 
            AND BIZ_ORDER_CANCEL_RECORD.ORDER_NO = BIZ_ORDER_ITEM.ORDER_NO 
            AND SYS_CODE_VALUE.TYPE_ID = '1023' 
            AND BIZ_CUSTOMER.ID = BIZ_ORDER.CUSTOMER_ID
            AND BIZ_CUSTOMER_BLGCTY.CST_ID = BIZ_ORDER.CUSTOMER_ID
            AND RES_CITY.CITY_CODE = BIZ_CUSTOMER_BLGCTY.REGISTER_CITY
            AND SYS_CODE_VALUE.CODE_VALUE = BIZ_ORDER.ORDER_STATUS 
            AND TO_CHAR( BIZ_ORDER_ITEM.CREATE_TIME, 'yyyy-mm-dd' ) >= '{}'
            AND TO_CHAR( BIZ_ORDER_ITEM.CREATE_TIME, 'yyyy-mm-dd' ) <= '{}'
        """,
    "fields":
        ["LICENSE",
         "VEHICLE_TYPE_NAME",
         "ORDER_NO",
         "CUSTOMER_NAME",
         "CONTACTOR_PHONE",
         "CUSTOMER_HEAD",
         "ROLE",
         "REG_TIME",
         "CITY_NAME",
         "REAL_GET_TIME",
         "REAL_RET_TIME",
         "GET_STATION_NAME",
         "RET_STATION_NAME",
         "STATION_NAME",
         "STATION_ADDRESS",
         "IS_GUARDING",
         "USE_TYPE",
         "CREATE_TIME",
         "MOD_TIME",
         "PAY_TYPE",
         "ORDER_STATUS",
         "TOTAL_AMOUNT",
         "TOTAL_REDUCE_AMOUNT",
         "PAYED_AMOUNT",
         "ACTION_TIME",
         "CONTENT"],
}

vehicle_base_info_sql = {
    "sql": """
        SELECT
            RES_VEHICLE_BASE.LICENSE,
            RES_VEHICLE_RENT.VIN_CODE,
        CASE
                RES_VEHICLE_RENT.VEHICLE_PURPOSE 
                WHEN '1000' THEN
                '短租车' 
                WHEN '1001' THEN
                '合作车' 
                WHEN '1002' THEN
                '公务车' 
                WHEN '1003' THEN
                '代步车' 
                WHEN '1004' THEN
                '长租车' ELSE '' 
            END VEHICLE_PURPOSE,
        CASE
            RES_VEHICLE_BASE.LICENSE_COLOR 
            WHEN 1 THEN
            '蓝色' 
            WHEN 2 THEN
            '黄色' ELSE '' 
            END LICENSE_COLOR,
        CASE
            RES_VEHICLE_BASE.VEHICLE_COLOR 
            WHEN 1000 THEN
            '活力橙' 
            WHEN 1001 THEN
            '优雅白' 
            WHEN 1002 THEN
            '中国红' 
            WHEN 1003 THEN
            '乌木黑' 
            WHEN 1004 THEN
            '罗兰紫' 
            WHEN 1005 THEN
            '摩卡棕' 
            WHEN 1006 THEN
            '流沙金' 
            WHEN 1007 THEN
            '格调灰' 
            WHEN 1008 THEN
            '星辉银' 
            WHEN 1009 THEN
            '深海蓝' ELSE '' 
            END VEHICLE_COLOR,
            RES_VEHICLE_BASE.CREATE_TIME AS VEHICLE_CREATE_TIME,
            SYS_CODE_VALUE.CODE_NAME AS OPERATE_STATUS,
            BIZ_INSURANCE_COMPUL.START_TIME,
            BIZ_INSURANCE_COMPUL.END_TIME,
            SYS_ORGNIZATION.ORG_NAME
        FROM
            PANDA.RES_VEHICLE_BASE RES_VEHICLE_BASE,
            PANDA.RES_VEHICLE_RENT RES_VEHICLE_RENT,
            PANDA.SYS_CODE_VALUE SYS_CODE_VALUE,
            PANDA.BIZ_INSURANCE_COMPUL BIZ_INSURANCE_COMPUL,
            PANDA.SYS_ORGNIZATION SYS_ORGNIZATION
        WHERE
            RES_VEHICLE_BASE.ID = RES_VEHICLE_RENT.VEHICLE_ID 
            AND SYS_CODE_VALUE.TYPE_ID = '1036' 
            AND SYS_CODE_VALUE.CODE_VALUE = RES_VEHICLE_RENT.OPERATE_STATUS 
            AND BIZ_INSURANCE_COMPUL.PLATE_NUMBER = RES_VEHICLE_BASE.LICENSE
            AND RES_VEHICLE_BASE.ORGANIZATION_ID = SYS_ORGNIZATION.ID
    """,
    "fields":
        ["LICENSE",
         "VIN_CODE",
         "VEHICLE_PURPOSE",
         "LICENSE_COLOR",
         "VEHICLE_COLOR",
         "VEHICLE_CREATE_TIME",
         "OPERATE_STATUS",
         "START_TIME",
         "END_TIME",
         "ORG_NAME"
         ],
}

dispatcher_sql = {
    "sql": """
        SELECT
            SHEET_ID,
            CURRENT_ACCEPTER_NAME,
            CURRENT_ACCEPTER_ID,
            ACCEPT_ORGAN_NAME,
            CLOSE_TIME,
            SHEET_PROPERTIES,
            SHEET_MEMO,
            SHEET_STATE,
            BIZ_CFG_NAME,
            SHEET_ZONE,
            CREATE_ORGAN_NAME,
            CREATOR_NAME,
            CREATE_TIME 
        FROM
            tb_work_sheet 
        WHERE
            CLOSE_DATE >= '{}' 
            AND CLOSE_DATE <= '{}' 
    """,
    "fields": [
        "SHEET_ID",
        "CURRENT_ACCEPTER_NAME",
        "CURRENT_ACCEPTER_ID",
        "ACCEPT_ORGAN_NAME",
        "CLOSE_TIME",
        "SHEET_PROPERTIES",
        "SHEET_MEMO",
        "SHEET_STATE",
        "BIZ_CFG_NAME",
        "SHEET_ZONE",
        "CREATE_ORGAN_NAME",
        "CREATOR_NAME",
        "CREATE_TIME"
    ]
}

customer_RFM_sql = {
    "sql":
        """
        SELECT
            BO.CUSTOMER_NAME,
            BO.CONTACTOR_PHONE,
            BO.CITY_CODE,
            MAX( BO.TRADETIME ),
            TO_NUMBER(TO_DATE(to_char( MAX( BO.TRADETIME ), 'yyyy-mm-dd' ),'yyyy-mm-dd hh24:mi:ss')- TO_DATE('{}','yyyy-mm-dd hh24:mi:ss')) AS R,
            COUNT( BO.ORDER_NO ) AS F,
            SUM( BO.PAYED_AMOUNT ) AS M
        FROM
            PANDA.BIZ_ORDER BO 
        WHERE
            BO.CITY_CODE IN ( '400000', '650000' ) 
            AND to_char( BO.TRADETIME, 'yyyy-mm-dd' ) > '{}' 
            AND to_char( BO.TRADETIME, 'yyyy-mm-dd' ) < '{}' 
            AND BO.ORDER_STATUS IN ('1070','1050')
        GROUP BY
            BO.CUSTOMER_NAME,
            BO.CONTACTOR_PHONE,
            BO.CITY_CODE

        """,
    "fields":
        ["CUSTOMER_NAME",
         "CONTACTOR_PHONE",
         "CITY_CODE",
         "F",
         "M",
         "R"],
}

license_log_sql = {
    "sql":
        """
        SELECT
            BIZ_EDIT_INFO.ID,
            RES_VEHICLE_BASE.LICENSE,
            SYS_CODE_VALUE.CODE_NAME || '---->' || SYS_CODE_VALUE1.CODE_NAME AS CONTENT,
            BIZ_EDIT_INFO.USERNAME,
            BIZ_EDIT_INFO.CURRENT_TIME AS ACTION_TIME
        FROM
            PANDA.BIZ_EDIT_INFO BIZ_EDIT_INFO,
            PANDA.SYS_CODE_VALUE SYS_CODE_VALUE,
            PANDA.SYS_CODE_VALUE SYS_CODE_VALUE1,
            PANDA.RES_VEHICLE_BASE RES_VEHICLE_BASE 
        WHERE
            SYS_CODE_VALUE.TYPE_ID = '1036' 
            AND SYS_CODE_VALUE1.TYPE_ID = '1036' 
            AND RES_VEHICLE_BASE.ID = BIZ_EDIT_INFO.VEHICLE_ID 
            AND SYS_CODE_VALUE.CODE_VALUE = BIZ_EDIT_INFO.BEFORE_DESCRIPTION_INFO 
            AND SYS_CODE_VALUE1.CODE_VALUE = BIZ_EDIT_INFO.AFTER_DESCRIPTION_INFO 
            AND TO_CHAR( BIZ_EDIT_INFO.CURRENT_TIME, 'yyyy-mm-dd' ) >= '{}'
            AND TO_CHAR( BIZ_EDIT_INFO.CURRENT_TIME, 'yyyy-mm-dd' ) <= '{}'

        """,
    "fields":
        [
            "NO",
            "LICENSE",
            "CONTENT",
            "USERNAME",
            "ACTION_TIME",
        ],
}

user_log_sql = {
    "sql":
        """
            SELECT
                BIZ_ORDER_ITEM.LICENSE,
                BIZ_ORDER_ITEM.VEHICLE_TYPE_NAME,
                BIZ_ORDER.ORDER_NO,
                BIZ_ORDER.CUSTOMER_NAME,
                BIZ_ORDER.CONTACTOR_PHONE,
                CONCAT( BIZ_ORDER.CUSTOMER_NAME, BIZ_ORDER.CONTACTOR_PHONE ) AS CUSTOMER_HEAD,
                CONCAT( '用', '户' ) AS ROLE,
                BIZ_CUSTOMER.REG_TIME,
                RES_CITY.CITY_NAME,
                BIZ_ORDER_ITEM.REAL_GET_TIME,
                BIZ_ORDER_ITEM.REAL_RET_TIME,
                RSS1.STATION_NAME AS GET_STATION_NAME,
                RSS2.STATION_NAME AS RET_STATION_NAME,
                RSS1.STATION_NAME,
                RSS1.STATION_ADDRESS,
            CASE
                    RSS1.IS_GUARDING 
                    WHEN 0 THEN
                    '没有' 
                    WHEN 1 THEN
                    '有' ELSE '' 
                END IS_GUARDING,
            CASE
                RSS1.USE_TYPE 
                WHEN 0 THEN
                '普通' 
                WHEN 1 THEN
                '换电站' 
                WHEN 2 THEN
                '充电站' ELSE '' 
                END USE_TYPE,
                BIZ_ORDER.CREATE_TIME,
                BIZ_ORDER.MOD_TIME,
                BIZ_ORDER.PAY_TYPE,
                SYS_CODE_VALUE.CODE_NAME AS ORDER_STATUS,
                BIZ_ORDER.TOTAL_AMOUNT,
                BIZ_ORDER.TOTAL_REDUCE_AMOUNT,
                BIZ_ORDER.PAYED_AMOUNT,
                BIZ_FREEZE_AND_SHIELD_LOG.OPERATE_TIME,
            CASE
                    BIZ_FREEZE_AND_SHIELD_LOG.STATUS 
                    WHEN '1' THEN
                    '(' || BIZ_FREEZE_AND_SHIELD_REASON.REASON || ')' || BIZ_FREEZE_AND_SHIELD_LOG.REMARK || ':' || BIZ_FREEZE_AND_SHIELD_LOG.OPERATOR_NAME || '执行账户冻结' 
                    WHEN '2' THEN
                    '(' || BIZ_FREEZE_AND_SHIELD_REASON.REASON || ')' || BIZ_FREEZE_AND_SHIELD_LOG.REMARK || ':' || BIZ_FREEZE_AND_SHIELD_LOG.OPERATOR_NAME || '执行账户拉黑' ELSE NULL 
                END CONTENT 
            FROM
                PANDA.BIZ_ORDER BIZ_ORDER,
                PANDA.BIZ_FREEZE_AND_SHIELD_LOG BIZ_FREEZE_AND_SHIELD_LOG,
                PANDA.SYS_CODE_VALUE SYS_CODE_VALUE,
                PANDA.BIZ_CUSTOMER BIZ_CUSTOMER,
                PANDA.BIZ_CUSTOMER_BLGCTY BIZ_CUSTOMER_BLGCTY,
                PANDA.RES_CITY RES_CITY,
                PANDA.BIZ_FREEZE_AND_SHIELD_REASON BIZ_FREEZE_AND_SHIELD_REASON,
                PANDA.BIZ_ORDER_ITEM BIZ_ORDER_ITEM
                LEFT JOIN PANDA.RES_SERVICE_STATION RSS1 ON BIZ_ORDER_ITEM.GET_STATION_ID = RSS1.ID
                LEFT JOIN PANDA.RES_SERVICE_STATION RSS2 ON BIZ_ORDER_ITEM.REAL_RET_STATION_ID = RSS2.ID 
            WHERE
                BIZ_ORDER.ORDER_NO = BIZ_ORDER_ITEM.ORDER_NO 
                AND BIZ_FREEZE_AND_SHIELD_LOG.ORDER_NO = BIZ_ORDER_ITEM.ORDER_NO 
                AND SYS_CODE_VALUE.TYPE_ID = '1023' 
                AND BIZ_FREEZE_AND_SHIELD_REASON.REASON_CODE = BIZ_FREEZE_AND_SHIELD_LOG.OPERATE_REASON 
                AND BIZ_CUSTOMER.ID = BIZ_ORDER.CUSTOMER_ID 
                AND BIZ_CUSTOMER_BLGCTY.CST_ID = BIZ_ORDER.CUSTOMER_ID 
                AND RES_CITY.CITY_CODE = BIZ_CUSTOMER_BLGCTY.REGISTER_CITY 
                AND SYS_CODE_VALUE.CODE_VALUE = BIZ_ORDER.ORDER_STATUS 
                AND TO_CHAR( BIZ_FREEZE_AND_SHIELD_LOG.OPERATE_TIME, 'yyyy-mm-dd' ) >= '{}' 
                AND TO_CHAR( BIZ_FREEZE_AND_SHIELD_LOG.OPERATE_TIME, 'yyyy-mm-dd' ) <= '{}'
        """,
    "fields":
        ["LICENSE",
         "VEHICLE_TYPE_NAME",
         "ORDER_NO",
         "CUSTOMER_NAME",
         "CONTACTOR_PHONE",
         "CUSTOMER_HEAD",
         "ROLE",
         "REG_TIME",
         "CITY_NAME",
         "REAL_GET_TIME",
         "REAL_RET_TIME",
         "GET_STATION_NAME",
         "RET_STATION_NAME",
         "STATION_NAME",
         "STATION_ADDRESS",
         "IS_GUARDING",
         "USE_TYPE",
         "CREATE_TIME",
         "MOD_TIME",
         "PAY_TYPE",
         "ORDER_STATUS",
         "TOTAL_AMOUNT",
         "TOTAL_REDUCE_AMOUNT",
         "PAYED_AMOUNT",
         "ACTION_TIME",
         "CONTENT"],
}

user_relieve_log_sql = {
    "sql":
        """
            SELECT
                BIZ_ORDER_ITEM.LICENSE,
                BIZ_ORDER_ITEM.VEHICLE_TYPE_NAME,
                BIZ_ORDER.ORDER_NO,
                BIZ_ORDER.CUSTOMER_NAME,
                BIZ_ORDER.CONTACTOR_PHONE,
                CONCAT( BIZ_ORDER.CUSTOMER_NAME, BIZ_ORDER.CONTACTOR_PHONE ) AS CUSTOMER_HEAD,
                CONCAT( '用', '户' ) AS ROLE,
                BIZ_CUSTOMER.REG_TIME,
                RES_CITY.CITY_NAME,
                BIZ_ORDER_ITEM.REAL_GET_TIME,
                BIZ_ORDER_ITEM.REAL_RET_TIME,
                RSS1.STATION_NAME AS GET_STATION_NAME,
                RSS2.STATION_NAME AS RET_STATION_NAME,
                RSS1.STATION_NAME,
                RSS1.STATION_ADDRESS,
            CASE
                    RSS1.IS_GUARDING 
                    WHEN 0 THEN
                    '没有' 
                    WHEN 1 THEN
                    '有' ELSE '' 
                END IS_GUARDING,
            CASE
                RSS1.USE_TYPE 
                WHEN 0 THEN
                '普通' 
                WHEN 1 THEN
                '换电站' 
                WHEN 2 THEN
                '充电站' ELSE '' 
                END USE_TYPE,
                BIZ_ORDER.CREATE_TIME,
                BIZ_ORDER.MOD_TIME,
                BIZ_ORDER.PAY_TYPE,
                SYS_CODE_VALUE.CODE_NAME AS ORDER_STATUS,
                BIZ_ORDER.TOTAL_AMOUNT,
                BIZ_ORDER.TOTAL_REDUCE_AMOUNT,
                BIZ_ORDER.PAYED_AMOUNT,
                BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_OPERATE_TIME,
            CASE
                    BIZ_FREEZE_AND_SHIELD_LOG.STATUS 
                    WHEN '1' THEN
                    '(' || BIZ_FREEZE_AND_SHIELD_REASON.REASON || ')' || BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_REMARK || ':' || BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_OPERATOR_NAME || '执行解除冻结' 
                    WHEN '2' THEN
                    '(' || BIZ_FREEZE_AND_SHIELD_REASON.REASON || ')' || BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_REMARK || ':' || BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_OPERATOR_NAME || '执行解除拉黑' ELSE NULL 
                END CONTENT 
            FROM
                PANDA.BIZ_ORDER BIZ_ORDER,
                PANDA.BIZ_FREEZE_AND_SHIELD_LOG BIZ_FREEZE_AND_SHIELD_LOG,
                PANDA.SYS_CODE_VALUE SYS_CODE_VALUE,
                PANDA.BIZ_CUSTOMER BIZ_CUSTOMER,
                PANDA.BIZ_CUSTOMER_BLGCTY BIZ_CUSTOMER_BLGCTY,
                PANDA.BIZ_FREEZE_AND_SHIELD_REASON BIZ_FREEZE_AND_SHIELD_REASON,
                PANDA.RES_CITY RES_CITY,
                PANDA.BIZ_ORDER_ITEM BIZ_ORDER_ITEM
                LEFT JOIN PANDA.RES_SERVICE_STATION RSS1 ON BIZ_ORDER_ITEM.GET_STATION_ID = RSS1.ID
                LEFT JOIN PANDA.RES_SERVICE_STATION RSS2 ON BIZ_ORDER_ITEM.REAL_RET_STATION_ID = RSS2.ID 
            WHERE
                BIZ_ORDER.ORDER_NO = BIZ_ORDER_ITEM.ORDER_NO 
                AND BIZ_FREEZE_AND_SHIELD_LOG.ORDER_NO = BIZ_ORDER_ITEM.ORDER_NO 
                AND BIZ_FREEZE_AND_SHIELD_REASON.REASON_CODE = BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_OPERATE_REASON 
                AND SYS_CODE_VALUE.TYPE_ID = '1023' 
                AND BIZ_CUSTOMER.ID = BIZ_ORDER.CUSTOMER_ID 
                AND BIZ_CUSTOMER_BLGCTY.CST_ID = BIZ_ORDER.CUSTOMER_ID 
                AND RES_CITY.CITY_CODE = BIZ_CUSTOMER_BLGCTY.REGISTER_CITY 
                AND SYS_CODE_VALUE.CODE_VALUE = BIZ_ORDER.ORDER_STATUS 
                AND BIZ_FREEZE_AND_SHIELD_LOG.IS_RELIEVE = 1 
                AND TO_CHAR( BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_OPERATE_TIME, 'yyyy-mm-dd' ) >= '{}' 
                AND TO_CHAR( BIZ_FREEZE_AND_SHIELD_LOG.RELIEVE_OPERATE_TIME, 'yyyy-mm-dd' ) <= '{}'
        """,
    "fields":
        ["LICENSE",
         "VEHICLE_TYPE_NAME",
         "ORDER_NO",
         "CUSTOMER_NAME",
         "CONTACTOR_PHONE",
         "CUSTOMER_HEAD",
         "ROLE",
         "REG_TIME",
         "CITY_NAME",
         "REAL_GET_TIME",
         "REAL_RET_TIME",
         "GET_STATION_NAME",
         "RET_STATION_NAME",
         "STATION_NAME",
         "STATION_ADDRESS",
         "IS_GUARDING",
         "USE_TYPE",
         "CREATE_TIME",
         "MOD_TIME",
         "PAY_TYPE",
         "ORDER_STATUS",
         "TOTAL_AMOUNT",
         "TOTAL_REDUCE_AMOUNT",
         "PAYED_AMOUNT",
         "ACTION_TIME",
         "CONTENT"],
}

order_flow = {
    "sql": order_flow_sql.get("sql"),
    "fields": order_flow_sql.get("fields"),
    "es_fields": {
        "HEAD": "LICENSE",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "订单",
        "DATA_TYPE": "车辆",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

order_cancle = {
    "sql": order_cancle_sql.get("sql"),
    "fields": order_cancle_sql.get("fields"),
    "es_fields": {
        "HEAD": "LICENSE",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "订单",
        "DATA_TYPE": "车辆",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

user_order_flow = {
    "sql": order_flow_sql.get("sql"),
    "fields": order_flow_sql.get("fields"),
    "es_fields": {
        "HEAD": "CUSTOMER_HEAD",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "订单",
        "DATA_TYPE": "用户",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

user_order_cancle = {
    "sql": order_cancle_sql.get("sql"),
    "fields": order_cancle_sql.get("fields"),
    "es_fields": {
        "HEAD": "CUSTOMER_HEAD",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "订单",
        "DATA_TYPE": "用户",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

station_order_flow = {
    "sql": order_flow_sql.get("sql"),
    "fields": order_flow_sql.get("fields"),
    "es_fields": {
        "HEAD": "STATION_NAME",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "订单",
        "DATA_TYPE": "站点",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

station_order_cancle = {
    "sql": order_cancle_sql.get("sql"),
    "fields": order_cancle_sql.get("fields"),
    "es_fields": {
        "HEAD": "STATION_NAME",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "订单",
        "DATA_TYPE": "站点",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

vehicle_base_info = {
    "sql": vehicle_base_info_sql.get("sql"),
    "fields": vehicle_base_info_sql.get("fields"),
    "es_fields": {
        "HEAD": "LICENSE",
        "DATA": "",
    },
    "index": vehicle_base_info_index
}

license_dispatcher = {
    "sql": dispatcher_sql.get("sql"),
    "fields": dispatcher_sql.get("fields"),
    "es_fields": {
        "HEAD": "LICENSE",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "SHEET_ID",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "调度工单",
        "DATA_TYPE": "车辆",
        "DATA_SOURCE": "调度系统",
        "DATA": "",
    },
    "index":
        es_index}

user_dispatcher = {
    "sql": dispatcher_sql.get("sql"),
    "fields": dispatcher_sql.get("fields"),
    "es_fields": {
        "HEAD": "CUSTOMER_HEAD",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "SHEET_ID",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "调度工单",
        "DATA_TYPE": "用户",
        "DATA_SOURCE": "调度系统",
        "DATA": "",
    },
    "index":
        es_index}

station_dispatcher = {
    "sql": dispatcher_sql.get("sql"),
    "fields": dispatcher_sql.get("fields"),
    "es_fields": {
        "HEAD": "STATION_NAME",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "SHEET_ID",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "调度工单",
        "DATA_TYPE": "站点",
        "DATA_SOURCE": "调度系统",
        "DATA": "",
    },
    "index":
        es_index}

license_qiyu_worksheet = {
    "sql": "",
    "fields": "",
    "es_fields": {
        "HEAD": "车牌号",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "工单号",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "客服工单",
        "DATA_TYPE": "车辆",
        "DATA_SOURCE": "七鱼客服",
        "DATA": "",
    },
    "index":
        es_index}

user_qiyu_worksheet = {
    "sql": "",
    "fields": "",
    "es_fields": {
        "HEAD": "CUSTOMER_HEAD",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "工单号",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "客服工单",
        "DATA_TYPE": "用户",
        "DATA_SOURCE": "七鱼客服",
        "DATA": "",
    },
    "index":
        es_index}

license_offline_worksheet = {
    "sql": "",
    "fields": "",
    "es_fields": {
        "HEAD": "车牌号",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "工单",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "七鱼工单",
        "DATA_TYPE": "车辆",
        "DATA_SOURCE": "线下数据",
        "DATA": "",
    },
    "index":
        es_index}

user_offline_worksheet = {
    "sql": "",
    "fields": "",
    "es_fields": {
        "HEAD": "CUSTOMER_HEAD",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "工单",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "七鱼工单",
        "DATA_TYPE": "用户",
        "DATA_SOURCE": "线下数据",
        "DATA": "",
    },
    "index":
        es_index}

license_log = {
    "sql": license_log_sql.get("sql"),
    "fields": license_log_sql.get("fields"),
    "es_fields": {
        "HEAD": "LICENSE",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "系统日志",
        "DATA_TYPE": "车辆",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

user_log = {
    "sql": user_log_sql.get("sql"),
    "fields": user_log_sql.get("fields"),
    "es_fields": {
        "HEAD": "CUSTOMER_HEAD",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "系统日志",
        "DATA_TYPE": "用户",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}

user_relieve_log = {
    "sql": user_relieve_log_sql.get("sql"),
    "fields": user_relieve_log_sql.get("fields"),
    "es_fields": {
        "HEAD": "CUSTOMER_HEAD",
        "ACTION_TIME": "ACTION_TIME",
        "NO": "ORDER_NO",
        # "CONTENT": "CONTENT",
        "NO_TYPE": "系统日志",
        "DATA_TYPE": "用户",
        "DATA_SOURCE": "用车后台",
        "DATA": "",
    },
    "index":
        es_index}
